# -*- coding:utf-8 -*-
## author : cypro666
## note   : python3.4+
"""
simple wrapper of mysql operations, based on pymysql
"""
import sys
from threading import Lock
from pymysql import connections
from pymysql import err as mysqlerr
# from pymysql.cursors import SSCursor
from getpass import getpass

from .debug import time_meter, print_exception
from .utilities import DummyLock

__all__ = ['OpMysql']


class OpMysql(object):
    """ simple wrapper of mysql operations, based on pymysql """
    
    def __init__(self, **kwargs):
        """ `kwargs` is a dict(always from json) like: 
            {
                "host"     : "192.168.1.24",
                "post"     : 3306,
                "username" : "root"
                "password" : "bingo321"
                "database" : "mytest"
            }
        """
        self.__con = None
        if 'lock' in kwargs and kwargs['lock']:
            self.__lock = Lock()
        else:
            self.__lock = DummyLock()
        try:
            user = kwargs['username']
            passwd = kwargs['password']
            host, port, dbname= kwargs['host'], kwargs['port'], kwargs['database']
            sys.stdout.write('Try to connect '+user+'@'+host+':'+str(port)+' '+dbname+'\n')
            self.__con = connections.Connection(host = str(host), port = int(port), 
                                                user = str(user), passwd = str(passwd), 
                                                database = str(dbname), charset = 'utf8')
        except Exception:
            raise RuntimeError('OpMysql: connect failed!')
        self.__cur = self.__con.cursor()
    
    
    def __del__(self):
        """ close in release """
        if self.__con:
            self.__con.commit()
            self.__cur.close()
            self.__con.close()
        sys.stdout.write('OpMysql: connection closed!\n')
    
    def select_database(self, dbname):
        """ change database """
        with self.__lock:
            self.__con.select_db(dbname)
    
    
    def execute(self, sql, args = None):
        """ execute SQL on selected database with commit """
        with self.__lock:
            try:
                cur = self.__con.cursor()
                cur.execute(sql, args = args)
                self.__con.commit()  
                cur.close()          
            except mysqlerr.Error:
                print_exception('OpMysql.query')
            except Exception as e:
                raise e
            
    def execute_nocommit(self, sql, args = None):
        """ execute SQL on selected database without commit """
        with self.__lock:
            try:
                self.__cur.execute(sql, args = args)
            except mysqlerr.Error:
                print_exception('OpMysql.query')
            except Exception as e:
                raise e
            
    
    def commit(self):
        """ COMMIT """
        with self.__lock:
            self.__con.commit()
    
    
    def fetch(self, sql, args = None, size = 0):
        """ fetch data using SQL, always for SELECT tasks """
        with self.__lock:
            cur = self.__con.cursor()
            cur.execute(sql, args = args)
            try:
                if size <= 0 : 
                    items = cur.fetchall()
                else:
                    items = cur.fetchmany(size)
                cur.close()
                return items
            except Exception:
                print_exception(sql)
                cur.close()
                return None

    
    def query(self, sql, buffered = True):
        """ only for simple query, return raw results """
        with self.__lock:
            try:
                return self.__con.query(sql, unbuffered = not buffered)
            except Exception:
                print_exception('OpMysql.query')



@time_meter(__name__)
def test():
    dbf = OpMysql(username='root', password=getpass(),
                  host='localhost', port='3306', database='mysql')
    dbf.select_database('mysql')
    
    print(dbf.query("""SELECT * FROM `user` LIMIT %d;""" % (10,), True))

    sql = """SELECT * FROM `user` WHERE host=%s;"""
    cur = dbf.fetch(sql, ("localhost",))
    for i in cur:
        print(i)
    
    result = dbf.fetch(sql = """SELECT COUNT(*) FROM `user`""", args = None, size = -1)
    print(result)







